package dao.impl;

import dao.ShoppingMemberDao;
import pojo.*;
import util.BaseDao;
import util.PageBean;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 *用于实现ShoopingMember接口对购物车，会员的数据进行处理增删改等操作
 * 姚波
 */
public class ShoppingMemberDaoImpl extends BaseDao implements ShoppingMemberDao{
    /**
     * 用于获取数据库表中试图的数据
     *
     * @return
     */
    @Override
    public PageBean<HarvestAddress> membershipData(PageBean<HarvestAddress> me,int index) {
        List<HarvestAddress> list=new ArrayList<>();
        PreparedStatement pre=null;
        ResultSet re=null;

        try {
            String sql="select *  from harvestAddress where member_Id=?";
            pre=getConnection().prepareStatement(sql);
            pre.setInt(1,index);
            re=pre.executeQuery();
            while (re.next()){
                HarvestAddress h=new HarvestAddress();
                h.setHarve_Id(re.getInt("harve_Id"));
                h.setHarve_Address(re.getString("harve_Address"));
                h.setHarve_Region(re.getString("harve_Region"));
                list.add(h);
            }
            me.setList(list);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            release(re,pre,getConnection());
        }
        return me;
    }
    /**
     * 获取指定的对象的数据进行修改
     *
     * @param parseInt
     * @return
     */
    @Override
    public User appoint(int parseInt) {
        User me=null;
        PreparedStatement pre=null;
        ResultSet re=null;
        Connection conn=getConnection();
        try {
            String sql="select * from e_user where u_id=?";
            pre=conn.prepareStatement(sql);
            pre.setInt(1,parseInt);
            re=pre.executeQuery();
            while (re.next()){
                User m=new User();
                m.setuID(re.getInt("u_id"));
                m.setuAccount(re.getString("u_account"));
                m.setuPassword(re.getString("u_password"));
                m.setuNickName(re.getString("u_nickname"));
                m.setuGender(re.getInt("u_gender"));
                m.setuPhoneNumber(re.getString("u_phonenumber"));
                m.setuEmail(re.getString("u_email"));
                m.setuAddress(re.getString("u_address"));
                m.setuIntegral(re.getInt("u_integral"));
                m.setuRegdate(re.getDate("u_regdate"));
                me=m;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            release(null,pre,conn);
        }
        return me;
    }

    /**
     *修改会员信息表数据
     * @param use
     */
    public void memberModification(User use) {
        PreparedStatement pre=null;
        Connection conn=getConnection();
        try {
            String sql="update e_user set u_account=?,u_nickname=?,u_gender=?,u_phonenumber=?,u_email=?,u_address=?,u_integral=? where u_id=?";
            pre=conn.prepareStatement(sql);
            pre.setString(1,use.getuAccount());
            pre.setString(2,use.getuNickName());
            pre.setInt(3,use.getuGender());
            pre.setString(4,use.getuPhoneNumber());
            pre.setString(5,use.getuEmail());
            pre.setString(6,use.getuAddress());
            pre.setInt(7,use.getuIntegral());
            pre.setInt(8,use.getuID());
            pre.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            release(null,pre,conn);
        }
    }

    /**
     * 修改收获地址信息
     *
     * @param har
     */
    @Override
    public void setShouHuo(HarvestAddress har) {
        PreparedStatement pre=null;
        Connection conn=getConnection();
        try {
            String sql="update harvestAddress set harve_Region=?,harve_Address=? where harve_Id=?";
            pre=conn.prepareStatement(sql);
            pre.setString(1,har.getHarve_Region());
            pre.setString(2,har.getHarve_Address());
            pre.setInt(3,har.getHarve_Id());
            pre.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            release(null,pre,conn);
        }
    }

    /**
     * 修改订单的信息
     *
     * @param or
     */
    @Override
    public void dingDan(Order or) {
        PreparedStatement pre=null;
        Connection conn=getConnection();

        try {
            String sql="update orders set ORDERS_PRICE=?,ORDERS_STATE=?,ORDERS_ADDRESS=?,ORDERS_REMARKS=?,ORDERS_DATE=? where ORDERS_ID=?";
            pre=conn.prepareStatement(sql);
            pre.setInt(1,or.getOrderPrice());
            pre.setInt(2,or.getOrderState());
            pre.setString(3,or.getOrderAddress());
            pre.setString(4,or.getOrderRemarks());
            pre.setObject(5,or.getOrderDate());
            pre.setInt(6,or.getOrderId());
            pre.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            release(null,pre,conn);
        }
    }

    /**
     *删除会员信息
     */
    public void deleteMember(int u_id) {
        PreparedStatement pre=null;
        Connection conn=getConnection();
        try {
            String sql="delete from e_user where u_id=?";
            pre=conn.prepareStatement(sql);
            pre.setInt(1,u_id);
            pre.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            release(null,pre,conn);
        }

    }
    /**
     * 删除收获信息表
     */
    public void deleteHarvest(int harve_id) {
        PreparedStatement pre=null;
        Connection conn=getConnection();
        try {
            String sql="delete from harvestAddress where harve_Id=? or member_Id=?";
            pre=conn.prepareStatement(sql);
            pre.setInt(1,harve_id);
            pre.setInt(2,harve_id);
            pre.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            release(null,pre,conn);
        }
    }
    /**
     * 删除订单信息表
     */
    public void deleteOrder(int orders_id) {
        PreparedStatement pre=null;
        Connection conn=getConnection();
        try {
            String sql="delete from orders where ORDERS_ID=?";
            pre=conn.prepareStatement(sql);
            pre.setInt(1,orders_id);
            pre.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            release(null,pre,conn);
        }
    }

    /**
     * 获取对象数据进行导出
     * @param parseInt
     * @return
     */
    public User export(int parseInt) {
        User user=null;
        PreparedStatement pre=null;
        ResultSet re=null;
        Connection conn=getConnection();

        try {
            String sql="select * from e_user where u_id=?";
            pre=conn.prepareStatement(sql);
            pre.setInt(1,parseInt);
            re=pre.executeQuery();
            while (re.next()){
                User us=new User();
                us.setuID(re.getInt("u_id"));
                us.setuPassword(re.getString("u_phonenumber"));
                us.setuAddress(re.getString("u_account"));
                us.setuNickName(re.getString("u_nickname"));
                us.setuGender(re.getInt("u_gender"));
                us.setuPhoneNumber(re.getString("u_phonenumber"));
                us.setuEmail(re.getString("u_email"));
                us.setuAddress(re.getString("u_address"));
                us.setuIntegral(re.getInt("u_integral"));
                us.setuRegdate(re.getDate("u_regdate"));
                user=us;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            release(re,pre,conn);
        }
        return user;
    }

    /**
     * 获取会员所有信息资料
     * @param me
     * @param ename
     * @return
     */
    public PageBean<User> memberUser(PageBean<User> me, String ename) {
        List<User> list=new ArrayList<>();
        PreparedStatement pre=null;
        ResultSet re=null;

        try {

            String s="";
            if(ename!=null){
                s+="and u_nickname Like'%"+ename+"%'";
            }
            String sql="select * from (select rownum r,e_user.* from e_user where 1=1"+s+" and rownum<=?)where r>?";
            pre=getConnection().prepareStatement(sql);
            pre.setInt(1,me.getPageIndex()*me.getPageSize());
            pre.setInt(2,me.getPageSize()*(me.getPageIndex()-1));
            re=pre.executeQuery();
            while (re.next()){
                User m=new User();
                m.setuID(re.getInt("u_id"));
                m.setuAccount(re.getString("u_account"));
                m.setuPassword(re.getString("u_password"));
                m.setuNickName(re.getString("u_nickname"));
                m.setuGender(re.getInt("u_gender"));
                m.setuPhoneNumber(re.getString("u_phonenumber"));
                m.setuEmail(re.getString("u_email"));
                m.setuAddress(re.getString("u_address"));
                m.setuIntegral(re.getInt("u_integral"));
                m.setuRegdate(re.getDate("u_regdate"));
                list.add(m);
            }
            me.setList(list);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            release(re,pre,getConnection());
        }
        return me;
    }

    /**
     * 会员分页
     * @param me
     * @param ename
     */
    public PageBean<User> getcountss(PageBean<User> me, String ename) {
        PreparedStatement pre=null;
        ResultSet re=null;
        Connection conn=getConnection();
        int a=0;
        try {
            String s="";
            if(ename!=null){
                s+="and u_nickname Like'%"+ename+"%'";
            }
            String sql="select count(*) from e_user where 1=1"+s;
            pre=conn.prepareStatement(sql);
            re=pre.executeQuery();
            while (re.next()){
                a=re.getInt(1);
            }
            me.setTotalCount(a);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            release(re,pre,conn);
        }
        return me;
    }
    /**
     * 获取指定的收获地址对象
     * @param parseInt
     */
    public HarvestAddress addressModify(int parseInt) {
        HarvestAddress h1=null;
        PreparedStatement pre=null;
        ResultSet re=null;
        Connection conn=getConnection();
        try {
            String sql="select *  from harvestAddress where harve_Id=?";
            pre=conn.prepareStatement(sql);
            pre.setInt(1,parseInt);
            re=pre.executeQuery();
            while (re.next()){
                HarvestAddress h=new HarvestAddress();
                h.setHarve_Id(re.getInt("harve_Id"));
                h.setHarve_Address(re.getString("harve_Address"));
                h.setHarve_Region(re.getString("harve_Region"));
                h1=h;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            release(re,pre,conn);
        }
        return h1;
    }

    /**
     * 用于保存用户数据
     * @param us
     * @return
     */
    public int deposit(User us) {
        int a=0;
        PreparedStatement pre=null;
        Connection conn=getConnection();
        try {
            String sql="insert into e_user(u_id,u_account,u_password,u_nickname,u_gender,u_phonenumber,u_email,u_address,u_integral) values (?,?,?,?,?,?,?,?,?)";
            pre=conn.prepareStatement(sql);
            pre.setInt(1,us.getuID());
            pre.setString(2,us.getuAccount());
            pre.setString(3,us.getuPassword());
            pre.setString(4,us.getuNickName());
            pre.setInt(5,us.getuGender());
            pre.setString(6,us.getuPhoneNumber());
            pre.setString(7,us.getuEmail());
            pre.setString(8,us.getuAccount());
            pre.setInt(9,us.getuIntegral());
            a=pre.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            release(null,pre,conn);
        }
        return a;
    }

    /**
     * 用来获取指定购物车数据
     * @param parseInt
     * @return
     */
    public ShoppingCart sees(int parseInt) {
        ShoppingCart sho=null;
        PreparedStatement pre=null;
        ResultSet re=null;
        Connection conn=getConnection();
        try {
            String sql="select * from shoppingCart where member_Id=?";
            pre=conn.prepareStatement(sql);
            pre.setInt(1,parseInt);
            re=pre.executeQuery();
            while (re.next()){
                ShoppingCart sh=new ShoppingCart();
                sh.setCartId(re.getInt("cart_Id"));
                sh.setMemberId(re.getInt("member_Id"));
                sh.setCartTotal(re.getInt("cart_total"));
                sho=sh;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            release(re,pre,conn);
        }
        return sho;
    }

    /**
     * 获取指定的购物车关联表
     * @param cartId
     * @return
     */
    public List<RelationView> shooup(int cartId) {
        List<RelationView> list=new ArrayList<>();
        PreparedStatement pre=null;
        ResultSet re=null;
        Connection conn=getConnection();
        try {
            String sql="select * from relation_view where cart_Id=?";
            pre=conn.prepareStatement(sql);
            pre.setInt(1,cartId);
            re=pre.executeQuery();
            while (re.next()){
                RelationView as=new RelationView();
                as.setTableId(re.getInt("table_Id"));
                as.setCartId(re.getInt("cart_Id"));
                as.setComm(re.getInt("comm"));
                as.setTableAmount(re.getInt("table_amount"));
                as.setGoName(re.getString("go_name"));
                as.setGoSalesVolume(re.getInt("go_salesvolume"));
                as.setGoPraise(re.getInt("go_praise"));
                as.setGoUptime(re.getDate("go_uptime"));
                as.setGoPictureUrl(re.getString("go_pictureurl"));
                as.setGoDescribe(re.getString("go_describe"));
                as.setGoCost(re.getInt("go_cost"));
                as.setGoPrice(re.getInt("go_price"));
                as.setGoStock(re.getInt("go_stock"));
                as.setClId(re.getInt("cl_id"));
                as.setGoSecKill(re.getInt("go_seckill"));
                list.add(as);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            release(re,pre,conn);
        }
        return list;
    }

    /**
     * 获取商品中信息
     *
     * @param comm
     */
    @Override
    public Goods picture(int comm) {
        Goods go=null;
        PreparedStatement pre=null;
        ResultSet re=null;
        Connection conn=getConnection();
        try {
            String sql="select * from goods where go_id=?";
            pre=conn.prepareStatement(sql);
            pre.setInt(1,comm);
            re=pre.executeQuery();
            while (re.next()){
                Goods g=new Goods();
                g.setGoId(re.getInt("go_id"));
                g.setGoName(re.getString("go_name"));
                g.setGoSalesVolume(re.getInt("go_salesvolume"));
                g.setGoPraise(re.getInt("go_praise"));
                g.setGoUptime(re.getDate("go_uptime"));
                g.setGoPictureUrl(re.getString("go_pictureurl"));
                g.setGoDescribe(re.getString("go_describe"));
                g.setGoCost(re.getInt("go_cost"));
                g.setGoPrice(re.getInt("go_price"));
                g.setGoStock(re.getInt("go_stock"));
                g.setClId(re.getInt("cl_id"));
                g.setGoSecKill(re.getInt("go_seckill"));
                go=g;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            release(re,pre,conn);
        }
        return go;
    }

    /**
     * 删除购物车里的商品信息
     *
     * @param parseInt
     */
    @Override
    public int deleteCart(int parseInt) {
        int a=0;
        PreparedStatement pre=null;
        Connection conn=getConnection();
        try {
            String sql="delete from associationTable where table_Id=?";
            pre=conn.prepareStatement(sql);
            pre.setInt(1,parseInt);
            a=pre.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            release(null,pre,conn);
        }
        return a;
    }

    /**
     * 分页查询购物车信息
     *
     * @param pb
     * @param cartId
     * @return
     */
    @Override
    public PageBean PageShopMemList(PageBean pb, int cartId) {
        List<ShoppingCart> list=new ArrayList<ShoppingCart>();
        BaseDao bd=new BaseDao();
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;

        try {
            conn=bd.getConnection();
          /*  String s="";
            if (0!=cartId){
                s+=" and ename LIKE '%"+cartId+"%'";
            }
            String sql="SELECT * FROM ( " +
                    "  SELECT ROWNUM r,emp.* ,dname FROM emp " +
                    "  INNER JOIN dept ON emp.deptno=dept.deptno "  +
                    "  WHERE 1=1"+s+"and ROWNUM<=? " +
                    " )WHERE r>?";*/

            String sql="select * from(select rownum r,shoppingcart.* from shoppingcart) where r between ? and ?";

            ps=conn.prepareStatement(sql);
            ps.setInt(1,pb.getPageIndex()*pb.getPageSize());
            ps.setInt(2,pb.getPageSize()*(pb.getPageIndex()-1));
            rs=ps.executeQuery();
            while (rs.next()){
                ShoppingCart sc=new ShoppingCart();
                sc.setCartId(rs.getInt("cart_id"));
                sc.setCartTotal(rs.getInt("cart_total"));
                sc.setMemberId(rs.getInt("member_id"));
                list.add(sc);
            }
            //把数据封装在pageBean里面
            pb.setList(list);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return pb;
    }

    /**
     * 通过用户id创建购物车
     * @param id
     * @return
     * @author Jamin
     */

    @Override
    public int creatShoppingCart(int id) {
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        int a=0;
        try {
            conn=BaseDao.getConnection();
            String s="insert into shoppingCart values(Sequence_shoppingCart1.nextval,?,0)";

            ps=conn.prepareStatement(s);
            ps.setInt(1,id);
            a=ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
        }
        return a;
    }
}
